﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.ServiceModel.DomainServices.Hosting;
using System.ServiceModel.DomainServices.Server;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.ServiceModel.DomainServices.EntityFramework;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web.Hosting;

namespace DYSales.Web
{
    // TODO: Create methods containing your application logic.
    //[RequiresAuthentication]
    [EnableClientAccess()]
    public class DomainService1 : DbDomainService<EntityContext>
    {
        [Invoke]
        public DateTime GetST()
        {
            return DateTime.Now;
        }

        [Invoke]
        public totalclass GetTotalClassByTime(string op, DateTime start, DateTime end)
        {
            var lo = DbContext.SellTables.Where(p => p.st_消费时间 >= start && p.st_消费时间 <= end && p.st_操作员.Equals(op)).ToList();
            var sumObj = new totalclass();
            foreach (var item in lo)
            {
                sumObj.to_刷卡支付金额 += item.st_刷卡支付;
                sumObj.to_现金支付金额 += item.st_现金支付;
            }
            sumObj.to_总营业金额 = lo.Sum(s => s.st_实付金额);
            sumObj.to_操作员 = op;
            sumObj.to_交班时间 = DateTime.Now.ToString();
            sumObj.to_统计范围 = start.ToString() + "到" + end.ToString();
            return sumObj;
        }

        [Invoke]
        public totalObject GetTotalSellTablesByTime(DateTime start, DateTime end)
        {
            var lo = DbContext.SellTables.Where(p => p.st_消费时间 >= start && p.st_消费时间 <= end).ToList();
            DbContext.SellDetails.Load();
            var sumObj = new totalObject();
            foreach (var item in lo)
            {
                sumObj.to_刷卡支付金额 += item.st_刷卡支付;
                sumObj.to_现金支付金额 += item.st_现金支付;
                foreach (var sd in item.SellDetails)
                {
                    sumObj.to_商品成本总金额 += sd.sd_进货价 * sd.sd_数量;
                }
            }
            sumObj.to_总营业金额 = lo.Sum(s => s.st_实付金额);
            sumObj.to_商品利润结算 = sumObj.to_总营业金额 - sumObj.to_商品成本总金额;
            var le = DbContext.ExpenditureTables.Where(p => p.exp_时间 >= start && p.exp_时间 <= end).ToList();
            sumObj.to_支出总金额 = le.Sum(p => p.exp_支出金额);
            sumObj.to_营业结算 = sumObj.to_总营业金额 - sumObj.to_支出总金额 - sumObj.to_商品成本总金额;
            sumObj.to_统计时间 = DateTime.Now.ToString();
            sumObj.to_统计范围 = start.ToString() + "到" + end.ToString();
            return sumObj;
        }

        [Invoke]
        public bool CheckPwd(string userName, string pwd)
        {
            var us = this.DbContext.Operators.FirstOrDefault(u => u.ot_用户名.Equals(userName));
            if (us != null)
            {
                if (us.ot_密码.Equals(pwd))
                {
                    return true;
                }
            }
            return false;
        }

        [Invoke]
        public bool CheckSelledOp(string userName)
        {
            var us = this.DbContext.SellTables.FirstOrDefault(u => u.st_操作员.Equals(userName));
            if (us != null)
            {
                return true;
            }
            return false;
        }

        [Invoke]
        public string GetPR(string obj)
        {
            return other.GetFirstPinyin(obj);
        }

        #region 挂提单
        static List<SellDetail> sts = new List<SellDetail>();
        [Invoke]
        public List<SellDetail> GetSaveSell()
        {
            if (sts.Count > 0)
            {
                var st = sts.ToList();
                sts.Clear();
                return st;
            }
            return sts;
        }

        [Invoke]
        public void SetSaveSell(List<SellDetail> sells)
        {
            sts = new List<SellDetail>(sells);
        }
        #endregion

        [Invoke]
        public long bulkInsert(List<Operator> ops, string TableName)
        {
            var startTime = DateTime.Now;

            var bulkCopy = new SqlBulkCopy(conSQL.GetConString());
            bulkCopy.DestinationTableName = TableName;
            bulkCopy.WriteToServer(ops.AsDataReader());

            return DateTime.Now.Subtract(startTime).Ticks;
        }

        [Invoke]
        public bool checkPd(string pid, string pName)
        {
            return DbContext.PdTables.FirstOrDefault(v => v.p_商品编号.Equals(pid) || v.p_商品名称.Equals(pName)) == null ? true : false;
        }

        [Invoke]
        public List<PdTable> GetPdFilter(string search)
        {
            return this.DbContext.PdTables.Where(pd => pd.p_商品编号.StartsWith(search) || pd.p_商品名称.StartsWith(search)
                        || pd.p_拼音简码.StartsWith(search) || pd.p_条形码.Equals(search)).ToList();
        }

        [Invoke]
        public bool CheckPdIsSelledOrInven(List<string> PdNames)
        {
            var sells = from s in DbContext.SellDetails from p in PdNames where s.sd_商品名称.Equals(p) select s.sd_商品名称;
            if (sells.Count() > 0)
            {
                return false;
            }
            var pivs = from i in DbContext.PdInventorys from p in PdNames where i.p_商品名称.Equals(p) select i.p_商品名称;
            if (pivs.Count() > 0)
            {
                return false;
            }
            return true;
        }

        #region Excel操作

        string folder = HostingEnvironment.MapPath("~/imports");
        string GetFileName(string fileName)
        {
            return folder + @"\" + fileName;
        }

        //startRow以降序批定每个工作表从第几行开始数据导入
        [Invoke]
        public List<imp_table> Importfile(string fileName, int[] startRow, bool FirstSheet)
        {
            ExcelIO excel = new ExcelIO();
            var ds = excel.ImportExcel(GetFileName(fileName), FirstSheet);
            if (ds == null)
            {
                var msg = excel.errorMessage;
                return null;
            }
            List<imp_table> list = new List<imp_table>();
            if (FirstSheet)
            {
                imp_table it = new imp_table();
                it.name = ds.Tables[0].TableName;
                it.rows = new List<imp_row>();
                for (int i = startRow[0]; i < ds.Tables[0].Rows.Count; i++)
                {
                    imp_row ir = new imp_row();
                    ir.data = new List<string>();
                    foreach (var item in ds.Tables[0].Rows[i].ItemArray)
                    {
                        ir.data.Add(item.ToString());
                    }
                    it.rows.Add(ir);
                }
                list.Add(it);
            }
            else
            {
                for (int t = 0; t < ds.Tables.Count; t++)
                {
                    imp_table it = new imp_table();
                    it.name = ds.Tables[t].TableName;
                    it.rows = new List<imp_row>();
                    for (int i = startRow[t]; i < ds.Tables[t].Rows.Count; i++)
                    {
                        imp_row ip = new imp_row();
                        ip.data = new List<string>();
                        foreach (var item in ds.Tables[t].Rows[i].ItemArray)
                        {
                            ip.data.Add(item.ToString());
                        }
                        it.rows.Add(ip);
                    }
                    list.Add(it);
                }
            }

            File.Delete(GetFileName(fileName));
            return list;
        }
        //上传excel文件到服务器imports文件夹
        [Invoke]
        public void UploadFile(string fileName, byte[] context)
        {
            if (!Directory.Exists(folder))
            {
                Directory.CreateDirectory(folder);
            }

            string filePath = GetFileName(fileName);
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
            //写文件操作
            using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                fs.Write(context, 0, context.Length);
            }
        }

        #endregion

        [Query(IsDefault = true)]
        public IQueryable<Set_table> GetSet_tables()
        {
            return this.DbContext.Set_tables;
        }

        [Invoke]
        public Set_table GetSetTable()
        {
            return this.DbContext.Set_tables.First();
        }

        public void InsertSet_table(Set_table st)
        {
            DbEntityEntry<Set_table> entityEntry = this.DbContext.Entry(st);

            if ((entityEntry.State != EntityState.Detached))
            {
                entityEntry.State = EntityState.Added;
            }
            else
            {
                this.DbContext.Set_tables.Add(st);
            }
        }

        public void UpdateSet_table(Set_table st)
        {
            this.DbContext.Set_tables.AttachAsModified(st, this.ChangeSet.GetOriginal(st), this.DbContext);
        }

        public void DeleteSet_table(Set_table st)
        {
            DbEntityEntry<Set_table> entityEntry = this.DbContext.Entry(st);

            if ((entityEntry.State != EntityState.Deleted))
            {
                entityEntry.State = EntityState.Deleted;
            }
            else
            {
                this.DbContext.Set_tables.Attach(st);
                this.DbContext.Set_tables.Remove(st);
            }
        }

        [Invoke]
        public List<ExpenditureTable> GetExtFilter(string search)
        {
            return this.DbContext.ExpenditureTables.Where(x => x.exp_类别.StartsWith(search)).ToList();
        }

        [Query(IsDefault = true)]
        public IQueryable<Inventory> GetInventorys()
        {
            return this.DbContext.Inventorys;
        }

        public void InsertInventory(Inventory st)
        {
            DbEntityEntry<Inventory> entityEntry = this.DbContext.Entry(st);

            if ((entityEntry.State != EntityState.Detached))
            {
                entityEntry.State = EntityState.Added;
            }
            else
            {
                this.DbContext.Inventorys.Add(st);
            }
        }

        public void UpdateInventory(Inventory st)
        {
            this.DbContext.Inventorys.AttachAsModified(st, this.ChangeSet.GetOriginal(st), this.DbContext);
        }

        public void DeleteInventory(Inventory st)
        {
            DbEntityEntry<Inventory> entityEntry = this.DbContext.Entry(st);

            if ((entityEntry.State != EntityState.Deleted))
            {
                entityEntry.State = EntityState.Deleted;
            }
            else
            {
                this.DbContext.Inventorys.Attach(st);
                this.DbContext.Inventorys.Remove(st);
            }
        }

        [Query(IsDefault = true)]
        public IQueryable<PdInventory> GetPdInventorys()
        {
            return this.DbContext.PdInventorys;
        }

        public void InsertPdInventory(PdInventory st)
        {
            DbEntityEntry<PdInventory> entityEntry = this.DbContext.Entry(st);

            if ((entityEntry.State != EntityState.Detached))
            {
                entityEntry.State = EntityState.Added;
            }
            else
            {
                this.DbContext.PdInventorys.Add(st);
            }
        }

        public void UpdatePdInventory(PdInventory st)
        {
            this.DbContext.PdInventorys.AttachAsModified(st, this.ChangeSet.GetOriginal(st), this.DbContext);
        }

        public void DeletePdInventory(PdInventory st)
        {
            DbEntityEntry<PdInventory> entityEntry = this.DbContext.Entry(st);

            if ((entityEntry.State != EntityState.Deleted))
            {
                entityEntry.State = EntityState.Deleted;
            }
            else
            {
                this.DbContext.PdInventorys.Attach(st);
                this.DbContext.PdInventorys.Remove(st);
            }
        }

        [Query(IsDefault = true)]
        public IQueryable<ExpenditureTable> GetExpenditureTables()
        {
            return this.DbContext.ExpenditureTables;
        }

        public IQueryable<ExpenditureTable> GetExpenditureTableByTime(DateTime start, DateTime end)
        {
            return DbContext.ExpenditureTables.Where(p => p.exp_时间 >= start && p.exp_时间 <= end);
        }

        public void InsertExpenditureTable(ExpenditureTable st)
        {
            DbEntityEntry<ExpenditureTable> entityEntry = this.DbContext.Entry(st);

            if ((entityEntry.State != EntityState.Detached))
            {
                entityEntry.State = EntityState.Added;
            }
            else
            {
                this.DbContext.ExpenditureTables.Add(st);
            }
        }

        public void UpdateExpenditureTable(ExpenditureTable st)
        {
            this.DbContext.ExpenditureTables.AttachAsModified(st, this.ChangeSet.GetOriginal(st), this.DbContext);
        }

        public void DeleteExpenditureTable(ExpenditureTable st)
        {
            DbEntityEntry<ExpenditureTable> entityEntry = this.DbContext.Entry(st);

            if ((entityEntry.State != EntityState.Deleted))
            {
                entityEntry.State = EntityState.Deleted;
            }
            else
            {
                this.DbContext.ExpenditureTables.Attach(st);
                this.DbContext.ExpenditureTables.Remove(st);
            }
        }

        [Query(IsDefault = true)]
        public IQueryable<StockAdd> GetStockAdds()
        {
            return this.DbContext.StockAdds.OrderBy(p => p.id);
        }

        public void InsertStockAdd(StockAdd ot)
        {
            DbEntityEntry<StockAdd> entityEntry = this.DbContext.Entry(ot);

            if ((entityEntry.State != EntityState.Detached))
            {
                entityEntry.State = EntityState.Added;
            }
            else
            {
                this.DbContext.StockAdds.Add(ot);
            }
        }

        public void UpdateStockAdd(StockAdd ot)
        {
            this.DbContext.StockAdds.AttachAsModified(ot, this.ChangeSet.GetOriginal(ot), this.DbContext);
        }

        public void DeleteStockAdd(StockAdd ot)
        {
            DbEntityEntry<StockAdd> entityEntry = this.DbContext.Entry(ot);

            if ((entityEntry.State != EntityState.Deleted))
            {
                entityEntry.State = EntityState.Deleted;
            }
            else
            {
                this.DbContext.StockAdds.Attach(ot);
                this.DbContext.StockAdds.Remove(ot);
            }
        }

        [Query(IsDefault = true)]
        public IQueryable<StockDel> GetStockDels()
        {
            return this.DbContext.StockDels.OrderBy(p => p.id);
        }

        public void InsertStockDel(StockDel ot)
        {
            DbEntityEntry<StockDel> entityEntry = this.DbContext.Entry(ot);

            if ((entityEntry.State != EntityState.Detached))
            {
                entityEntry.State = EntityState.Added;
            }
            else
            {
                this.DbContext.StockDels.Add(ot);
            }
        }

        public void UpdateStockDel(StockDel ot)
        {
            this.DbContext.StockDels.AttachAsModified(ot, this.ChangeSet.GetOriginal(ot), this.DbContext);
        }

        public void DeleteStockDel(StockDel ot)
        {
            DbEntityEntry<StockDel> entityEntry = this.DbContext.Entry(ot);

            if ((entityEntry.State != EntityState.Deleted))
            {
                entityEntry.State = EntityState.Deleted;
            }
            else
            {
                this.DbContext.StockDels.Attach(ot);
                this.DbContext.StockDels.Remove(ot);
            }
        }

        [Query(IsDefault = true)]
        public IQueryable<SellTable> GetSellTables()
        {
            return this.DbContext.SellTables.Include("SellDetails").OrderBy(p => p.id);
        }

        public IQueryable<SellTable> GetSellTablesByTimeName(string ot_userName, DateTime start, DateTime end)
        {
            return this.DbContext.SellTables.Where(p => p.st_操作员.Equals(ot_userName)).Where(p => p.st_消费时间 >= start && p.st_消费时间 <= end).Include("SellDetails");
        }

        public IQueryable<SellTable> GetSellTablesByTime(DateTime start, DateTime end)
        {
            return this.DbContext.SellTables.Where(p => p.st_消费时间 >= start && p.st_消费时间 <= end).Include("SellDetails");
        }

        public void InsertSellTable(SellTable ot)
        {
            DbEntityEntry<SellTable> entityEntry = this.DbContext.Entry(ot);

            if ((entityEntry.State != EntityState.Detached))
            {
                entityEntry.State = EntityState.Added;
            }
            else
            {
                this.DbContext.SellTables.Add(ot);
            }
        }

        public void UpdateSellTable(SellTable ot)
        {
            this.DbContext.SellTables.AttachAsModified(ot, this.ChangeSet.GetOriginal(ot), this.DbContext);
        }

        public void DeleteSellTable(SellTable ot)
        {
            DbEntityEntry<SellTable> entityEntry = this.DbContext.Entry(ot);
            if ((entityEntry.State != EntityState.Deleted))
            {
                entityEntry.State = EntityState.Deleted;
            }
            else
            {
                this.DbContext.SellTables.Attach(ot);
                this.DbContext.SellTables.Remove(ot);
            }
        }

        [Query(IsDefault = true)]
        public IQueryable<SellDetail> GetSellDetails()
        {
            return this.DbContext.SellDetails.OrderBy(p => p.id);
        }

        public void InsertSellDetail(SellDetail qt)
        {
            DbEntityEntry<SellDetail> entityEntry = this.DbContext.Entry(qt);

            if ((entityEntry.State != EntityState.Detached))
            {
                entityEntry.State = EntityState.Added;
            }
            else
            {
                this.DbContext.SellDetails.Add(qt);
            }
        }

        public void UpdateSellDetail(SellDetail qt)
        {
            this.DbContext.SellDetails.AttachAsModified(qt, this.ChangeSet.GetOriginal(qt), this.DbContext);
        }

        public void DeleteSellDetail(SellDetail qt)
        {
            DbEntityEntry<SellDetail> entityEntry = this.DbContext.Entry(qt);

            if ((entityEntry.State != EntityState.Deleted))
            {
                entityEntry.State = EntityState.Deleted;
            }
            else
            {
                this.DbContext.SellDetails.Attach(qt);
                this.DbContext.SellDetails.Remove(qt);
            }
        }


        [Query(IsDefault = true)]
        public IQueryable<PdTable> GetPdTables()
        {
            return this.DbContext.PdTables.OrderBy(p => p.id);
        }

        public IQueryable<PdTable> GetSetPdtable()//商品超库存下限
        {
            return from p in DbContext.PdTables where p.p_库存数量 <= p.p_库存下限 select p;
        }

        public IQueryable<PdTable> GetExtPdtable(List<string> objs)//商品名称
        {
            return from p in DbContext.PdTables from c in objs where p.p_商品名称.Equals(c) select p;
        }

        public void InsertPdTable(PdTable ot)
        {
            DbEntityEntry<PdTable> entityEntry = this.DbContext.Entry(ot);

            if ((entityEntry.State != EntityState.Detached))
            {
                entityEntry.State = EntityState.Added;
            }
            else
            {
                this.DbContext.PdTables.Add(ot);
            }
        }

        public void UpdatePdTable(PdTable ot)
        {
            this.DbContext.PdTables.AttachAsModified(ot, this.ChangeSet.GetOriginal(ot), this.DbContext);
        }

        public void DeletePdTable(PdTable ot)
        {
            DbEntityEntry<PdTable> entityEntry = this.DbContext.Entry(ot);

            if ((entityEntry.State != EntityState.Deleted))
            {
                entityEntry.State = EntityState.Deleted;
            }
            else
            {
                this.DbContext.PdTables.Attach(ot);
                this.DbContext.PdTables.Remove(ot);
            }
        }

        [Query(IsDefault = true)]
        public IQueryable<Operator> GetOperators()
        {
            return this.DbContext.Operators.Include("QcTables").OrderBy(p => p.id);
        }

        public void InsertOperator(Operator ot)
        {
            DbEntityEntry<Operator> entityEntry = this.DbContext.Entry(ot);

            if ((entityEntry.State != EntityState.Detached))
            {
                entityEntry.State = EntityState.Added;
            }
            else
            {
                this.DbContext.Operators.Add(ot);
            }
        }

        public void UpdateOperator(Operator ot)
        {
            this.DbContext.Operators.AttachAsModified(ot, this.ChangeSet.GetOriginal(ot), this.DbContext);
        }

        public void DeleteOperator(Operator ot)
        {
            DbEntityEntry<Operator> entityEntry = this.DbContext.Entry(ot);

            if ((entityEntry.State != EntityState.Deleted))
            {
                entityEntry.State = EntityState.Deleted;
            }
            else
            {
                this.DbContext.Operators.Attach(ot);
                this.DbContext.Operators.Remove(ot);
            }
        }

        [Query(IsDefault = true)]
        public IQueryable<QcTable> GetQcTables()
        {
            return this.DbContext.QcTables.OrderBy(p => p.id);
        }

        public void InsertQcTable(QcTable qt)
        {
            DbEntityEntry<QcTable> entityEntry = this.DbContext.Entry(qt);

            if ((entityEntry.State != EntityState.Detached))
            {
                entityEntry.State = EntityState.Added;
            }
            else
            {
                this.DbContext.QcTables.Add(qt);
            }
        }

        public void UpdateQcTable(QcTable qt)
        {
            this.DbContext.QcTables.AttachAsModified(qt, this.ChangeSet.GetOriginal(qt), this.DbContext);
        }

        public void DeleteQcTable(QcTable qt)
        {
            DbEntityEntry<QcTable> entityEntry = this.DbContext.Entry(qt);

            if ((entityEntry.State != EntityState.Deleted))
            {
                entityEntry.State = EntityState.Deleted;
            }
            else
            {
                this.DbContext.QcTables.Attach(qt);
                this.DbContext.QcTables.Remove(qt);
            }
        }

    }
    #region other
    public class imp_table
    {
        public string name { get; set; }
        public List<imp_row> rows { get; set; }
    }

    public class imp_row
    {
        public List<string> data { get; set; }
    }
    #endregion
}


